package com.kgc.dao;

import com.kgc.entity.User;
import com.kgc.untils.PageUtils;
import org.apache.commons.lang3.StringUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserMgrDao extends BaseDao {
    /**
     * 联表查询
     *
     * @param pageUtils
     * @param name
     * @return
     */
    public List<User> selectAllUser(PageUtils pageUtils, String name) {
        List<User> users = new ArrayList<>();
        try {
            String sql = "select u.id,u.username,u.phone,u.sex from User u";
            if (StringUtils.isNotEmpty(name)) {
                sql += " where  username like '%" + name + "%'";
            }
            sql += " limit ?,?";
            ResultSet rs = super.getExecuteQuery(sql, pageUtils.start(), pageUtils.getPageSize());
            while (rs.next()) {

                users.add(new User(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4)));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
           super.closeConnection();
        }

        return users;
    }


    /**
     * 查总数
     *
     * @param name
     * @return
     */
    public int totalCount(String name) {
        try {
            String sql = "select count(*) from user";
            if (StringUtils.isNotEmpty(name)) {
                sql += " where  username like '%" + name + "%'";
            }
            ResultSet rs = super.getExecuteQuery(sql);
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
           super.closeConnection();
        }
        return 0;
    }

    /**
     * 添加用户
     *
     * @param user
     * @return
     */
    public int addUser(User user) {
        String sql = "INSERT INTO `user`( `username`, `phone`, `sex`, `status`) VALUES ( ?,?,?,1)";
        return super.getExecuteUpdate(sql, user.getUsername(), user.getPhone(), user.getSex());

    }

    /**
     * 删除用户
     *
     * @param id
     * @return
     */
    public int deleteUser(String id) {
        String sql = "DELETE FROM `user` WHERE `id` = ?";
        return super.getExecuteUpdate(sql, id);
    }

    /**
     * 修改用户
     *
     * @param user
     * @return
     */
    public int editUser(User user) {
        String sql = "UPDATE `user` SET `username` = ?,`phone` = ?, `sex` = ? WHERE `id` =? ";
        return super.getExecuteUpdate(sql, user.getUsername(), user.getPhone(), user.getSex(), user.getId());
    }


    /**
     * 改变表car里status的状态
     * @param carName
     * @return
     */
    public int stopCarstatus(String carName) {
        String sql = "UPDATE `car` SET  `status` = 0 WHERE car.`name`=?";
        return super.getExecuteUpdate(sql,carName);
    }

    /**
     * 改变表user_car里status的状态
     * @param carName
     * @return
     */
    public int stopuUserCarstatus(String carName) {
        String sql = "UPDATE `user_car` SET  `status` = 1 WHERE user_car.car_id=(SELECT car.id FROM car WHERE car.name=?)";
        return super.getExecuteUpdate(sql,carName);
    }

    /**
     *  改变表room里status的状态
     * @param name
     * @return
     */
    public int stopRoomstatus(String name ,String danyuan) {
        String sql = "UPDATE `room` SET  `status` = 0 WHERE room.name =? AND danyuan_id = (SELECT danyuan.id FROM danyuan WHERE danyuan.name=?)";
        return super.getExecuteUpdate(sql,name,danyuan);
    }

    /**
     *  改变表user_room里status的状态
     * @param name
     * @return
     */
    public int stopuUserRoomstatus(String name,String danyuan) {
        String sql = "UPDATE `user_room` SET  `status` = 0 WHERE room_id=(SELECT room.id FROM room WHERE room.name=? AND  danyuan_id = (SELECT danyuan.id FROM danyuan WHERE danyuan.name=?))";
        return super.getExecuteUpdate(sql,name,danyuan);
    }
}
